﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using webapi.Entity;

namespace webapi.Models
{
  public class UserService
  {
    //获取全部用户
    static public DataRowCollection getAllUser()
    {
      string sql = "select * from [user]";
      return DB.ExecuteSelectSql(sql).Tables[0].Rows;
    }

    static public DataRowCollection getAllStudent()
    {
      string sql = "select * from [user] where type='3'";
      return DB.ExecuteSelectSql(sql).Tables[0].Rows;
    }

    internal static int addUser(User user, string pwd)
    {
      string sql = string.Format("insert into [user](user_id, pwd, name, email, type) values('{0}', '{1}', '{2}', '{3}', '{5})", user.user_id, pwd, user.name, user.email, user.type);
      return DB.ExecuteSql(sql);
    }

    internal static int deleteUser(string id)
    {
      string sql = "delete from [user] where user_id ='" + id + "'";
      return DB.ExecuteSql(sql);
    }

    internal static int updateUser(User user)
    {
      string sql = string.Format("update [user] set user_id='{0}', name='{2}', email='{3}' where user_id='{0}'", user.user_id, user.name, user.email);
      return DB.ExecuteSql(sql);
    }

    internal static int updateUserOfPwd(string id, string pwd)
    {
      string sql = string.Format("update [user] set pwd='{0}' where user_id='{1}'", pwd, id);
      return DB.ExecuteSql(sql);
    }

    internal static int resetUser(string id)
    {
      string sql = string.Format("update [user] set pwd='E10ADC3949BA59ABBE56E057F20F883E' where user_id='{0}'", id);
      return DB.ExecuteSql(sql);
    }

    internal static DataRow getStudentById(string id)
    {
      string sql = "select a.stu_id, b.name, b.email, a.class_id, c.grade, c.name, c.subject, a.choose_num, a.chose_num, c.teach_id, c.instructor_id, c.department from student as a join [user] as b on a.stu_id = b.user_id join team as c on a.class_id = c.class_id where a.stu_id = '" + id + "'";
      return DB.ExecuteSelectSql(sql).Tables[0].Rows[0];
    }

    public static int getStudentChoseNumById(string id)
    {
      string sql = "select sum(a.point) from course as a join set_up_course as b on a.course_id=b.course_id join select_course as c on b.class_course_id=c.class_course_id where c.stu_id='" + id + "'";
      return int.Parse("0" + DB.ExecuteSelectSql(sql).Tables[0].Rows[0][0]);
    }
  }
}